package com.chen.invoicing;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import util.ExcelUtils;

public class DBTools {  
  
    private java.sql.Connection connection;  
    private java.sql.PreparedStatement preparedStatement;  
  
    public DBTools() {  
        try {  
            connection = DriverManager.getConnection(  
                    "jdbc:mysql://192.168.0.248/test?useUnicode=true&characterEncoding=UTF-8", "root", "a+rquQe5KMGKKkmFJkl0");  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
    }  
  
    static {  
        try {  
            Class.forName("com.mysql.jdbc.Driver");  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
    }  
  
    public void close() {  
        if (connection != null) {  
            try {  
                connection.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
        }  
    }  
  
    public ResultSet executeQuery(String sql, LinkedList<Object> params) {  
        try {  
            preparedStatement = connection.prepareStatement(sql);  
            if (params != null) {  
                int i = 1;  
                for (Object p : params) {  
                    preparedStatement.setObject(i++, p);  
                }  
            }  
            return preparedStatement.executeQuery();  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
        return null;  
    }  
    
    public static void testJdb() throws Exception{
    	String[] cityMap = {"济南"};
    	
    	DBTools tool = new DBTools();  
    	
    	ResultSet rs = tool.executeQuery("SELECT `订单编号` from `民生长沙`", null);  
        while(rs.next()){
        	String OrderCode = rs.getString(1);
        }
    	
        tool.close();  
    }
    
	public static void testNewNo0810() throws Exception {
		DBTools tool = new DBTools();
		LinkedList<Object> params = new LinkedList<Object>();
		params.add(1);
		ResultSet rs = tool.executeQuery("SELECT 商品编号,批次号,原批次号 from 0810商品 WHERE 1=?", params);
		while (rs.next()) {
			StringBuilder result = new StringBuilder();
			String id = rs.getString(1);
			String newNo = rs.getString(2);
			String oldBatchNo = rs.getString(3);
			result.append("update swift_const_item set valued = '" + newNo
					+ "' where 'code'='cmbc_invoicing_params' and valued='" + oldBatchNo + "' and keyd='" + id + "';");
			System.out.println(result.toString());
		}
		tool.close();
	}
    
	private static void duizhang() throws Exception{
		//对账
    	String filePath = "C:\\Users\\chen\\Desktop\\民生8月对账\\进销存给的.xls";
		List<List<String>> xls = ExcelUtils.readXls(filePath);
        if(xls != null && !xls.isEmpty()){
        	int totalCount = xls.size();
        	for (List<String> xl : xls) {
        		String delivId = xl.get(0);
    			String OrderCode = xl.get(1);
    			OrderCode = OrderCode.substring(0,OrderCode.length() - 1);
    			String sql = "update `民生给的全部8月` set DeliverGoodsId='"+delivId+"' where left(订单编号,15)='"+OrderCode+"';";
    			System.out.println(sql);
            }
        }
//        	
//        	String sql1 = "select a.* from `民生长沙` a  left join `微能长沙进货单` b on a.DeliverGoodsId=b.民生订单号 where  b.民生订单号 is null";
//        	String sql2 = "select a.* from `微能长沙进货单` a  left join `民生长沙` b on b.DeliverGoodsId=a.民生订单号 where  b.DeliverGoodsId is null";
//        	/**
//        	 * String sql1=" select a.orderId from bill_backup a LEFT JOIN order_info b on a.orderId=b.orderId  "
//					+ " where a.billType="+BillBackup.BILL_UNIONPAY_SCANCODE+" and a.billDate=?"
//					+ " and b.orderId is null and (a.ext1 = ? and a.ext2 = ?)" //我方订单不存在 二维码支付
//        	 */
//        	
////        	xls.stream().forEach(xl -> {
////        		for(int i = 0;i<xl.size();i++){
////        			String delivId = xl.get(0);
////        			String OrderCode = xl.get(1);
////        			System.out.println(OrderCode);
////        		}
////        	});
//        }
	}
	
	public static void t1() throws Exception{
		//;
		DBTools tool = new DBTools();
		LinkedList<Object> params = new LinkedList<Object>();
		params.add("");
		
		List<String> apOrderIds = new ArrayList<String>();
		ResultSet rs = tool.executeQuery("SELECT 民生订单 from `我方非长沙8月` where ext4=?", params);
		while (rs.next()) {
			StringBuilder result = new StringBuilder();
			String apOrderId = rs.getString(1);
			apOrderIds.add(apOrderId);
			//SELECT 订单编号 from `民生给的长沙8月` where DeliverGoodsId=
		}
		
		
		for(String apOrderId : apOrderIds){
			params = new LinkedList<Object>();
			params.add(apOrderId);
			rs = tool.executeQuery("SELECT 订单编号 from `民生给的全部8月` where DeliverGoodsId=?", params);
			while (rs.next()) {
				String orderNo = rs.getString(1);
				//update `8月我方的长沙` set ext4=? where apOrderId = ?
				String sql = "update `我方非长沙8月` set ext4='"+orderNo+"' where 民生订单 ='" + apOrderId + "';";
    			System.out.println(sql);
			}
		}
		
		tool.close();
	}
	
	
	public static void testShanDong() throws Exception{
    	
    	DBTools tool = new DBTools();  
    	
    	ResultSet rs = tool.executeQuery("SELECT `orderId` from `山东移动`", null);  
    	StringBuilder sb = new StringBuilder();
        while(rs.next()){
        	String orderCode = rs.getString(1);
        	sb.append(orderCode).append(",");
        }
    	
        System.out.println(sb.toString());
        tool.close();  
    }
    
	public static void testPinganBank() throws Exception{
    	
    	DBTools tool = new DBTools();  
    	
    	ResultSet rs = tool.executeQuery("SELECT * from kfc济南平安", null);  
    	StringBuilder sb = new StringBuilder();
    	//<tr><td>青岛市</td><td>民主</td><td>甘肃省兰州市城关区皋兰路3号肯德基餐厅</td></tr>
        while(rs.next()){
        	String city = rs.getString(2);
        	String name = rs.getString(1);
        	String address = rs.getString(3);
        	sb.append("<tr><td>").append(city).append("</td><td>").append(name).append("</td><td>").append(address).append("</td></tr>");
        	sb.append("\n");
        }
    	
        System.out.println(sb.toString());
        tool.close();  
    }
	
    public static void main(String[] args) throws Exception {  
    	testPinganBank();
    	
    	/**
    	 * select a.* from `民生给的全部8月` a  left join 我方非长沙8月 b on a.订单编号=b.ext4 where  b.ext4 is null


select a.* from `我方非长沙8月` a  left join 民生给的全部8月 b on a.ext4=b.订单编号 where  b.订单编号 is null
    	 */
    	//select a.* from `民生给的10月其他全部` a  left join 我方10月民生其他全部 b on a.订单编号=b.ext4 where  b.ext4 is null and a.订单编号 not in (select 订单编号 from `民生给的长沙10月明细`);
    	//select a.* from `我方10月民生其他全部` a  left join 民生给的10月其他全部 b on a.ext4=b.订单编号 where  b.订单编号 is null and b.订单编号 not in (select 订单编号 from `民生给的长沙10月明细`);
    	
    } 
}  
